package com.document.service;

import com.document.entity.schema.*;
import com.document.dto.DatasourceConnection;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import java.sql.*;

@Slf4j
@Service
public class DatabaseMetadataService {

    public DatabaseSchema parseSchema(DatasourceConnection connection) throws SQLException {
        log.info("开始解析数据库结构: {}@{}:{}/{}",
                connection.getUsername(), connection.getHost(), connection.getPort(), connection.getDatabase());

        DatabaseSchema schema = new DatabaseSchema();

        try (Connection conn = DriverManager.getConnection(
                connection.getJdbcUrl(),
                connection.getUsername(),
                connection.getPassword())) {

            // 测试连接是否有效
            if (!conn.isValid(5)) {
                throw new SQLException("数据库连接无效");
            }

            DatabaseMetaData metaData = conn.getMetaData();
            schema.setDatabaseName(conn.getCatalog());
            schema.setDatabaseVersion(metaData.getDatabaseProductVersion());

            log.info("连接成功，开始获取表信息");
            parseTables(metaData, schema, connection.getDatabase());
            log.info("开始获取外键关系");
            parseForeignKeys(metaData, schema);

            log.info("数据库结构解析完成，共 {} 张表", schema.getTables().size());
        } catch (SQLException e) {
            log.error("数据库连接或解析失败", e);
            throw e;
        }

        return schema;
    }

    private void parseTables(DatabaseMetaData metaData, DatabaseSchema schema, String database) throws SQLException {
        try (ResultSet tables = metaData.getTables(database, null, "%", new String[]{"TABLE"})) {
            while (tables.next()) {
                Table table = new Table();
                table.setName(tables.getString("TABLE_NAME"));
                table.setComment(tables.getString("REMARKS"));
                table.setType(tables.getString("TABLE_TYPE"));

                parseColumns(metaData, table, database);
                parseIndexes(metaData, table, database);

                schema.getTables().add(table);
            }
        }
    }

    private void parseColumns(DatabaseMetaData metaData, Table table, String database) throws SQLException {
        try (ResultSet columns = metaData.getColumns(database, null, table.getName(), "%")) {
            while (columns.next()) {
                Column column = new Column();
                column.setName(columns.getString("COLUMN_NAME"));
                column.setType(columns.getString("TYPE_NAME"));
                column.setSize(columns.getInt("COLUMN_SIZE"));
                column.setDecimalDigits(columns.getInt("DECIMAL_DIGITS"));
                column.setNullable("YES".equals(columns.getString("IS_NULLABLE")));
                column.setDefaultValue(columns.getString("COLUMN_DEF"));
                column.setComment(columns.getString("REMARKS"));
                column.setAutoIncrement("YES".equals(columns.getString("IS_AUTOINCREMENT")));

                table.getColumns().add(column);
            }
        }

        try (ResultSet primaryKeys = metaData.getPrimaryKeys(database, null, table.getName())) {
            while (primaryKeys.next()) {
                String pkColumnName = primaryKeys.getString("COLUMN_NAME");
                table.getColumns().stream()
                        .filter(col -> pkColumnName.equals(col.getName()))
                        .forEach(col -> col.setPrimaryKey(true));
            }
        }
    }

    private void parseIndexes(DatabaseMetaData metaData, Table table, String database) throws SQLException {
        // 索引解析逻辑
    }

    private void parseForeignKeys(DatabaseMetaData metaData, DatabaseSchema schema) throws SQLException {
        for (Table table : schema.getTables()) {
            try (ResultSet importedKeys = metaData.getImportedKeys(null, null, table.getName())) {
                while (importedKeys.next()) {
                    ForeignKey fk = new ForeignKey();
                    fk.setFkTableName(table.getName());
                    fk.setFkColumnName(importedKeys.getString("FKCOLUMN_NAME"));
                    fk.setPkTableName(importedKeys.getString("PKTABLE_NAME"));
                    fk.setPkColumnName(importedKeys.getString("PKCOLUMN_NAME"));
                    fk.setFkName(importedKeys.getString("FK_NAME"));
                    schema.getForeignKeys().add(fk);
                }
            }
        }
    }
}